package org.tity.sql.dao.config;

import org.tity.commons.log.Logger;
import org.tity.sql.GlobalSettings;
import org.tity.sql.dao.Db2DAO;
import org.tity.sql.sql.builder.SQL;

public class TityDAO4Db2 extends Db2DAO implements TestDAO{
	public TityDAO4Db2()
	{
		super();
		
		//设置默认方言
		GlobalSettings.DEFAULT_SQL_DIALECT=this.getSQLDialect();
		//
		this.setDataSource((new Configs()).getDataSourceSampleDb2());
	}
 
	private String normalTable=null;
	private String pkTable=null;
	
	private String clobTableName=null;
	
	private String allTypeTableName=null;
	
	public String getAllTypeTableName() {
		return allTypeTableName;
	}
	 
	public String getClobTableName() {
		return clobTableName;
	}

	public String getNormalTableName() {
		return normalTable;
	}
	
	public String getPKTableName() {
		return pkTable;
	}

	public void setTableName(String normalTable,String pkTable,String clobTable,String allTypeTable) {
		this.normalTable = normalTable;
		this.pkTable=pkTable;
		this.clobTableName=clobTable;
		this.allTypeTableName=allTypeTable;
	}
 
	@Override
	public void createTables() {
		 
 
		dropTables();
		
		createNewsTable();
		//
		createPKTable();
		
		createClobTable();
		
		createAllTypeTable();
	}
	
	private void createAllTypeTable() {
		
		
		
	String[] lines= {
		"CREATE TABLE "+getAllTypeTableName()+" (",
		"ID INTEGER NOT NULL",
		",BLOB_value BLOB,",
		"CLOB_value CLOB,",
		"DECIMAL_value_l6_s2 DECIMAL(6,2),",
		"GRAPHIC_value_l64 GRAPHIC(64),",
		"CHAR_value_l64 CHAR(64),",
		"BIGINT_value BIGINT,",
		"TIME_value TIME,",
		"DATE_value DATE,",
		"SMALLINT_value SMALLINT,",
		"TIMESTAMP_value_l6 TIMESTAMP(6),",
		"REAL_value REAL,",
		"VARCHAR_value_l64 VARCHAR(64),",
		"DOUBLE_value DOUBLE,",
		"INTEGER_value INTEGER",
		")"
	};
		this.execute(SQL.joinSQLs(lines));
		//
		this.execute("COMMENT ON TABLE "+this.getAllTypeTableName()+" IS '联合主键表'");
		//
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".BLOB_value IS 'BLOB类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".CLOB_value IS 'CLOB类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".DECIMAL_value_l6_s2 IS 'DECIMAL类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".GRAPHIC_value_l64 IS 'GRAPHIC类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".CHAR_value_l64 IS 'CHAR类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".BIGINT_value IS 'BIGINT类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".TIME_value IS 'TIME类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".DATE_value IS 'DATE类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".SMALLINT_value IS 'SMALLINT类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".TIMESTAMP_value_l6 IS 'TIMESTAMP类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".REAL_value IS 'REAL类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".VARCHAR_value_l64 IS 'VARCHAR类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".DOUBLE_value IS 'DOUBLE类型字段'");
		this.execute("COMMENT ON COLUMN "+this.getAllTypeTableName()+".INTEGER_value IS 'INTEGER类型字段'");
		
	}

	private void createClobTable() {
		String[] lnsClob= {
				"CREATE TABLE "+getClobTableName()+" (",
				"  id varchar(64) not null constraint "+getClobTableName()+"_pk primary key,",
				"  content Clob",
				")"
		};
		
//		String[] pklnsClob= {
//				"ALTER TABLE "+getClobTableName()+" ADD (",
//				"  CONSTRAINT "+getClobTableName()+"_PK",
//				"  PRIMARY KEY  (id)",
//				"  ENABLE VALIDATE)"
//		};
		
		
		this.execute(SQL.joinSQLs(lnsClob));
//		this.execute(SQL.joinSQLs(pklnsClob));
		
		this.execute("comment on table "+getClobTableName()+" is '大内容表'");
		//
		this.execute("COMMENT ON COLUMN "+getClobTableName()+".id IS 'ID'");
		this.execute("COMMENT ON COLUMN "+getClobTableName()+".content IS '内容,单独存放的内容'");
		
		Logger.info("创建表 "+getClobTableName());
	}

	private void createPKTable() {
		String[] lnsPK = {
				"CREATE TABLE "+this.getPKTableName()+"  (",
				" bill_id int not null,",
				" owner_id int not null,",
				" type varchar(50) not null",
				" )"
		};
		
		this.execute(SQL.joinSQLs(lnsPK));
		this.execute("Alter TABLE "+this.getPKTableName()+"  add Constraint "+this.getPKTableName()+"_PK primary key(bill_id ,owner_id ,type)");
		
		this.execute("comment on table "+getPKTableName()+" is '关系表'");
		
		this.execute("COMMENT ON COLUMN "+getPKTableName()+".BILL_ID IS '单据号'");
		this.execute("COMMENT ON COLUMN "+getPKTableName()+".OWNER_ID IS '所有者'");
		this.execute("COMMENT ON COLUMN "+getPKTableName()+".type IS '类型'");
		
		Logger.info("创建表 "+getPKTableName());
	}

	private void createNewsTable() {
		String[] lns= {
				"CREATE TABLE "+getNormalTableName()+" (",
				" id int not null  generated by DEFAULT as identity  constraint "+getNormalTableName()+"_pk primary key,",
				"  code         varchar(36),",
				"  title        varchar(255),",
				"  publish_day  DATE,",
				"  enter_time   TIMESTAMP(6),",
				"  newsId         varchar(64),",
				"  alert_time time,",
				"  read_times   int,",
				"  price        DECIMAL(20,2),",
				"  create_by    varchar(36),",
				"  create_time  DATE,",
				"  deleted      int",
				")",
		};
		
		
		
		System.out.println(SQL.joinSQLs(lns));
		
		this.execute(SQL.joinSQLs(lns));
		Logger.info("创建表 "+getNormalTableName());
		
		
		this.execute("comment on table "+getNormalTableName()+" is '新闻表'");
		
		//
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".id IS 'ID'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".code IS '代码,业务代码'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".title IS '标题'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".publish_day IS '发布时间，新闻的实际发布时间'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".enter_time IS '输入时间，开始录入的时间'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".newsId IS '新闻内容ID'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".read_times IS '阅读次数'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".alert_time IS '提醒时间'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".price IS '单价，阅读计费'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".create_by IS '创建人'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".create_time IS '创建时间'");
		this.execute("COMMENT ON COLUMN "+getNormalTableName()+".deleted IS '是否已删'");
	}

	@Override
	public void dropTables() {

		if(this.isTableExists(getNormalTableName())) {
			Logger.info("删除表 "+getNormalTableName());
			this.execute("DROP TABLE "+getNormalTableName());
		}
		if(this.isTableExists(getPKTableName())) {
			Logger.info("删除表 "+getPKTableName());
			this.execute("DROP TABLE "+getPKTableName());
		}
		
		if(this.isTableExists(getClobTableName())) {
			Logger.info("删除表 "+getClobTableName());
			this.execute("DROP TABLE "+getClobTableName());
		}
		
		if(this.isTableExists(getAllTypeTableName())) {
			Logger.info("删除表 "+getAllTypeTableName());
			this.execute("DROP TABLE "+getAllTypeTableName());
		}
		
	}
}
